/**
 * Created by Roy on 16-8-28.
 */
/**
 * Created by yangzhao.lyz on 16/4/12.
 */
/* eslint-disable no-magic-numbers, no-console, no-unused-vars */
import mysql from 'mysql';


export default class Database {
    constructor(connInfo) {
        this.connInfo = connInfo;
    }

    db(action) {
        var conn = mysql.createConnection(this.connInfo);
        try {
            conn.connect();
            var r = action(conn);
            conn.end();
            return r;
        } catch (e) {
            console.error(new Date(), e);
            if (conn) {
                conn.end();
            }
        }
    }
    execTS(sql, ...values) {
        return this.exec(sql.join(' ? ', values));
    }
    exec(sql, values) {
        return new Promise((resolve, reject) => {
            this.db(function(conn) {
                try {
                    if (values == null) {
                        console.log(sql);
                    } else if (values instanceof Array) {
                        console.log(sql + '  <- [' + values.map(s => s == null ? 'null' : s.toString().length > 20 ? s.toString().substring(0, 17) + '...' : s) + ']');
                    } else {
                        console.log(sql + ' <- [' + (values.toString().length > 20 ? values.toString().substring(0, 17) + '...' : values) + ']');
                    }
                    conn.query(sql, values, function(err, rows, desc) {
                        if (err) {
                            reject(err);
                        } else {
                            resolve([rows, desc]);
                        }
                    });
                } catch (e) {
                    console.error(e);
                    reject(e);
                }
            });
        });
    }
    //region utils
    generateCondition(conditions, config) {
        if (conditions == null) {
            conditions = {};
        }
        if (config == null) {
            config = {};
        }
        var sql = [];
        var args = [];
        var start = true;
        for (var i in conditions) {
            if (!conditions.hasOwnProperty(i)) {
                continue;
            }
            var v = conditions[i];
            if (v == null) {
                continue;
            }
            if (i.charAt(0) == '&' || i.charAt(0) == '|') {
                if (!start) {
                    sql.push(' ', config.conjunction || 'AND', ' ');
                }
                var subsql = '';
                if (typeof v == 'function') {
                    subsql = v(i);
                } else if (v instanceof Array) {
                    subsql = this.generateCondition(v[0], v[1]);
                } else {
                    subsql = this.generateCondition(v, {
                        conjunction: i.charAt(0) == '|' ? 'OR' : 'AND'
                    });
                }
                if (subsql.sql != null && subsql.sql.trim()) {
                    sql.push(' ( ', subsql.sql, ' ) ');
                    args.push.apply(args, subsql.args);
                    start = false;
                }
            } else if (v instanceof Array) {
                if (start) {
                    start = false;
                } else {
                    sql.push(' ', config.conjunction || 'AND', ' ');
                }
                sql.push(' ', this.generateFields(i), ' ', v[0]);
                if (v[1]) {
                    args.push.apply(args, v.slice(1));
                }
            } else {
                if (start) {
                    start = false;
                } else {
                    sql.push(' ', config.conjunction || 'AND', ' ');
                }
                sql.push(' ', this.generateFields(i), '=? ');
                args.push(v);
            }
        }
        var sqlstr = sql.join('');
        if (sqlstr.trim() != '' && config.prependWhere) {
            sqlstr = ' WHERE ' + sqlstr;
        }
        return {
            sql: sqlstr,
            args
        };
    }

    generateConditionInline(args, cond, cfg) {
        if (cfg == null) {
            cfg = {};
        }
        if (cfg.prependWhere == null) {
            cfg.prependWhere = true;
        }
        var s = this.generateCondition(cond, cfg);
        if (s.sql == null || s.sql == '') {
            return '';
        }
        args.push.apply(args, s.args);
        return s.sql;
    }

    generateUpdateValue(data, config) {
        if (data == null) {
            data = {};
        }
        if (config == null) {
            config = {};
        }
        var sql = [];
        var args = [];
        var start = true;
        var key = config.key || 'id';
        for (var i in data) {
            if (!data.hasOwnProperty(i)) {
                continue;
            }
            if (i == key) {
                continue;
            }
            var v = data[i];
            if (v == null) {
                continue;
            }
            if (start) {
                start = false;
            } else {
                sql.push(',');
            }
            sql.push(' `', i, '`=?');
            args.push(v);
        }
        return {
            sql: sql.join(''),
            args
        };
    }

    generateInsertValue(data, config) {
        var sql1 = ['('];
        var sql2 = ['('];
        var args = [];
        var start = true;
        for (var i in data) {
            if (!data.hasOwnProperty(i)) {
                continue;
            }
            var v = data[i];
            if (v == null) {
                continue;
            }
            if (start) {
                start = false;
            } else {
                sql1.push(',');
                sql2.push(',');
            }
            sql1.push(' `', i, '`');
            sql2.push('?');
            args.push(v);
        }
        sql1.push(') ');
        sql2.push(') ');
        var s1 = sql1.join('');
        var s2 = sql2.join('');
        return {
            fields: s1,
            values: s2,
            sql: s1 + ' VALUES' + s2,
            args
        };
    }

    generateFields(fs) {
        if (fs == null) {
            return fs;
        }
        fs = fs.toString();
        var cols = fs.split(',');
        var ret = [];
        for (var col of cols) {
            col = col.trim().split('.').map(s => {
                if (s.match(/^\w+$/)) {
                    return '`' + s + '`';
                } else if (s.match(/^`\w+`$/)) {
                    return s;
                } else {
                    return null;
                }
            }).join('.');
            ret.push(col);
        }
        return ret.join();
    }

    generateOrder(fs) {
        if (fs == null) {
            return fs;
        }
        fs = fs.toString();
        if (!fs.trim().match(/^((([\w]+|`[\w]+`)(\s+asc|\s+desc)?)(,([\w]+|`[\w]+`)(\s+asc|\s+desc)?)*)$/i)) {
            return;
        }
        return fs;
    }

    generateLimit(fs) {
        if (fs == null) {
            return fs;
        }
        fs = fs.toString();
        if (!fs.trim().match(/^(\d+(,\d+)?)$/)) {
            return;
        }
        return fs;
    }

    like(name) {
        if (name) {
            return [" like concat('%',?,'%') ", name];
        }
    }
    //endregion

    deleteById(table, id) {
        if (id == null) {
            throw new Error('id cannot be null');
        }
        return this.exec('DELETE FROM `' + table + '` WHERE `id`=?', [id]);
    }

    del(table, conditions, config = {}) {
        var sql = ['DELETE FROM `', table, '` WHERE '];
        let g = this.generateCondition(conditions);
        if (g.sql) {
            sql.push(g.sql);
        }
        return this.exec(sql.join(''), g.args);
    }

    async queryById(table, id, config = {}) {
        let key = config.key || 'id';
        let [rows] = await this.exec('SELECT * FROM `' + table + '` WHERE `' + key + '`=?', [id]);
        return rows[0];
    }

    query(table, conditions = {}, config = {}) {
        let g = this.generateCondition(conditions, config);
        let s = ['SELECT '];
        if (config.columns) {
            s.push(config.columns.toString());
        } else {
            s.push('*');
        }
        if (!table.match(/\s/)) {
            s.push(' FROM `', table, '` ');
        } else {
            s.push(' FROM ', table, ' ');
        }
        if (g.sql) {
            s.push('WHERE ', g.sql);
        }
        if (config.groupBy) {
            s.push(' GROUP BY ', this.generateFields(config.groupBy));
        }
        if (config.orderBy) {
            s.push(' ORDER BY ', this.generateOrder(config.orderBy));
        }
        if (config.limit) {
            s.push(' LIMIT ', this.generateLimit(config.limit));
        }
        if (config.tail) {
            s.push(config.tail);
        }
        return this.exec(s.join(''), g.args);
    }

    async queryList(table, conditions, config) {
        return (await this.query(table, conditions, config))[0];
    }

    async queryOne(table, conditions, config) {
        if (config) {
            config.limit = 1;
        } else {
            config = {
                limit: 1
            };
        }
        let rows = await this.queryList(table, conditions, config);
        return rows && rows[0];
    }

    async update(table, data, conditions = {
        id: data && data.id
    }, config = {}) {
        var sql = ['UPDATE `', table, '` SET '];
        var g = this.generateUpdateValue(data, config);
        sql.push(g.sql);
        var args = g.args;
        g = this.generateCondition(conditions);
        if (g.sql) {
            sql.push(' WHERE ', g.sql);
        }
        args.push(...g.args);
        return (await this.exec(sql.join(''), args))[0];
    }


    async insert(table, data, config = {}) {
        var sql = ['INSERT ', config.ignore ? 'IGNORE ' : '', 'INTO `', table, '`'];
        var g = this.generateInsertValue(data, config);
        sql.push(g.sql);
        return (await this.exec(sql.join(''), g.args))[0];
    }

    async replace(table, data, config = {}) {
        var sql = ['REPLACE INTO `', table, '`'];
        var g = this.generateInsertValue(data, config);
        sql.push(g.sql);
        return (await this.exec(sql.join(''), g.args))[0];
    }

    async save(table, data, config = {}) {
        var key = config.key || ['id'];
        if (!(key instanceof Array)) {
            key = [key];
        }
        console.log(key, data);
        if (key.any(e => data[e] == null)) {
            return this.insert(table, data, config);
        } else {
            let cond = {};
            key.forEach(e => cond[e] = data[e]);
            return this.update(table, data, cond, config);
        }
    }
}
